package com.xjh.jparepository;

import com.xjh.bo.TestTable;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.SQLQuery;
import org.hibernate.type.LongType;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Repository
@Slf4j
public class TestSqlQuery {
    @PersistenceContext
    EntityManager em;

    public List<TestTable> test() {
        String sql = "select id, stu_id as stuId,money from test_table where id = ?1";
        Query query = em.createNativeQuery(sql);
        // 设置参数
        query.setParameter(1, "1");
        log.info("query===" + query);
        // 获取返回的字段，比如这里没获取money,则下面封装到实体的时候想取得object2就会报错obj[2]
        query.unwrap(SQLQuery.class).addScalar("id", LongType.INSTANCE);
        query.unwrap(SQLQuery.class).addScalar("stuId", LongType.INSTANCE);
        List result = query.getResultList();
        log.info("result===" + result);

        // 将object转成实体list
        List<TestTable> dataList = new ArrayList<>();
        for (Object objs : result) {
            TestTable testTable = new TestTable();
            Object[] obj = (Object[]) objs;
            testTable.setId(Objects.isNull(obj[0]) ? null : Integer.parseInt(obj[0].toString()));
            testTable.setStuId(Objects.isNull(obj[1]) ? null : Integer.parseInt(obj[1].toString()));
//            testTable.setMoney(String.valueOf(obj[2]));
            dataList.add(testTable);
        }
        log.info("dataList===" + dataList);
        /**测试将返回值自动封装成实体，避免手动对应*/
        // 注意不能命名别名（返回字段要和数据库对应，类似于rusultType,但是不能命名为驼峰的别名）,
        // 实体需要@Entity注解，（问题多表操作需要返回两个同名字段怎么办？再说吧）
        String sql1 = "select id, stu_id,money from test_table where id = ?1";
        Query query1 = em.createNativeQuery(sql1, TestTable.class);
        query1.setParameter(1, "1");
        // 还是一个object
        List<TestTable> res = query1.getResultList();
        log.info("res===" + res);
        return dataList;
    }
}
